package com.pur.zsms.shopmanage.dao.impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.pur.zsms.common.dao.GenericDao;
import com.pur.zsms.common.dao.JpaTemplate;
import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;
import com.pur.zsms.matermanage.mater.po.ShoppingCartMaterPo;
import com.pur.zsms.matermanage.mater.vo.ShoppingCartMaterVo;
import com.pur.zsms.ordercustommanage.po.OrderCustPo;
import com.pur.zsms.pmsnmanage.menu.po.MenuPo;
import com.pur.zsms.pmsnmanage.role.vo.RoleVo;
import com.pur.zsms.pmsnmanage.user.dao.IUserDao;
import com.pur.zsms.pmsnmanage.user.po.LdAddressPo;
import com.pur.zsms.pmsnmanage.user.po.UserLevelPo;
import com.pur.zsms.pmsnmanage.user.po.UserPo;
import com.pur.zsms.pmsnmanage.user.po.UserRolePo;
import com.pur.zsms.pmsnmanage.user.vo.UserVo;
import com.pur.zsms.shopmanage.dao.IShopDao;
import com.pur.zsms.shopmanage.po.ShopPo;
import com.pur.zsms.shopmanage.po.ShopRaisePricePo;
import com.pur.zsms.shopmanage.po.ShopUserPo;
import com.pur.zsms.shopmanage.vo.ShopRaisePriceVo;
import com.pur.zsms.shopmanage.vo.ShopVo;
import com.pur.zsms.supplier.po.SupplierPo;
import com.pur.zsms.systemsetting.datadictionary.po.CodeType;

@Repository("shopDao")
public class ShopDaoImpl extends GenericDao implements IShopDao {
	public PaginationSupport listShop(PageController pageController, ShopVo shopVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT s.shop_id, s.shop_name, (select company_name c from t_company c where c.company_id = s.company_user), GROUP_CONCAT( su.user_id, '-', u.real_name, '-', r.role_name, '<br/>' SEPARATOR '' ),ROUND(s.code+0) as code1 FROM t_shop s LEFT JOIN t_shop_user su ON s.shop_id = su.shop_id LEFT JOIN t_user u ON u.user_id = su.user_id LEFT JOIN t_user_role ur ON ur.user_id = u.user_id LEFT JOIN t_role r ON r.role_id = ur.role_id  ");
		if (shopVo.getCompanyId() != 0) {
			ql.append("    WHERE s.`company_user` = " + shopVo.getCompanyId() + " ");
		}
		ql.append(" GROUP BY s.shop_id  order By code1 ");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport listShopForSup(PageController pageController, ShopVo shopVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT s.shop_id, s.shop_name, (select company_name c from t_company c where c.company_id = s.company_user), GROUP_CONCAT( su.user_id, '-', u.real_name, '-', r.role_name, '<br/>' SEPARATOR '' ),(SELECT ss1.`code` FROM t_supplier_shop ss1 WHERE ss1.sup_id = '"
				+ shopVo.getSupId()
				+ "' AND ss1.shop_id =s.`shop_id`) AS code1  FROM t_shop s LEFT JOIN t_shop_user su ON s.shop_id = su.shop_id LEFT JOIN t_user u ON u.user_id = su.user_id LEFT JOIN t_user_role ur ON ur.user_id = u.user_id LEFT JOIN t_role r ON r.role_id = ur.role_id  ");
		ql.append("    WHERE 1=1 ");

		if (shopVo.getCompanyId() != 0) {
			ql.append("    AND s.`company_user` = " + shopVo.getCompanyId() + " ");
		}
		ql.append(" AND EXISTS ( SELECT 1 FROM t_supplier_shop ss WHERE ss.`sup_id` = '" + shopVo.getSupId() + "' AND ss.`shop_id`=s.`shop_id`)");
		ql.append(" GROUP BY s.shop_id  order By code1 ");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport shopRaiseList(PageController pageController, ShopRaisePriceVo shopRaisePriceVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT t.rpid, t.shop_id, (SELECT sh.shop_name FROM t_shop sh WHERE sh.shop_id = t.shop_id) AS a1, t.sup_id, (SELECT s.sup_companyname FROM t_supplier s WHERE s.sup_id = t.sup_id ) AS a2, t.mater_type, (SELECT describ FROM t_code WHERE code_type_id = 'mater_type' AND code_oder = t.mater_type) AS a3, t.raise_amount FROM t_shop_raise_price t  where t.shop_id = '"
				+ shopRaisePriceVo.getShopId() + "' ");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public void addShopPo(ShopPo c) throws GenericException {
		this.persist(c);
	}

	public void addShopUserPo(ShopUserPo c) throws GenericException {
		this.persist(c);
	}

	public void addShopRaisePricePo(ShopRaisePricePo c) throws GenericException {
		this.persist(c);
	}

	public void updateShopPo(ShopPo c) throws GenericException {
		this.merge(c);
	}

	public void updateShopUserPo(ShopUserPo c) throws GenericException {
		this.merge(c);
	}

	public void updateShopRaisePricePo(ShopRaisePricePo c) throws GenericException {
		this.merge(c);
	}

	public void deleteShopUserPoByShopId(long shopId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer sql = new StringBuffer();
		sql.append(" delete ShopUserPo o where o.shopId=" + shopId);
		template.executeUpdate(sql.toString());
	}

	public void deleteShopUserPoByUserId(long userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer sql = new StringBuffer();
		sql.append(" delete ShopUserPo o where o.userId=" + userId);
		template.executeUpdate(sql.toString());
	}

	public void deleteShopRaise(long rpid) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer sql = new StringBuffer();
		sql.append(" delete ShopRaisePricePo o where o.rpid=" + rpid);
		template.executeUpdate(sql.toString());
	}

	public ShopPo getShopVoByShopId(long shopId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer sql = new StringBuffer();
		sql.append(" select o ");
		sql.append(" from ShopPo o where o.shopId=" + shopId);
		List<ShopPo> list = template.find(sql.toString(), null);
		return list.get(0);
	}

	public List<ShopPo> getShopPoByShopName(String shopName) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer sql = new StringBuffer();
		sql.append(" select o ");
		sql.append(" from ShopPo o where o.shopName='" + shopName + "'");
		List<ShopPo> list = template.find(sql.toString(), null);
		return list;
	}

	public List<ShopPo> getAllShopPo() throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from ShopPo o  ");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopPo> getShopPoBycompanyId(String companyId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from ShopPo o where 1=1 ");
		if (!companyId.equals("0")) {
			ql.append(" and  o.companyUser in ( SELECT c.companyId FROM CompanyPo c WHERE c.companyId in ('" + companyId + "'))");
		}
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopPo> getShopPoBycompanyIdSupId(String companyId, long supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from ShopPo o where 1=1 ");
		if (!companyId.equals("0")) {
			ql.append(" and  o.companyUser in ( SELECT c.companyId FROM CompanyPo c WHERE c.companyId in ('" + companyId + "'))");
		}
		ql.append(" and exists( select 1 from SupplierShopPo d  where d.supId = '" + supId + "' and d.shopId = o.shopId	)");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopPo> getShopIdBycompanyIdAllSupId(String companyIdAll, long supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from ShopPo o where 1=1 ");
		if (!companyIdAll.equals("0")) {
			ql.append(" and  o.companyUser in ( SELECT c.companyId FROM CompanyPo c WHERE c.companyId in (" + companyIdAll + "))");
		}
		ql.append(" and exists( select 1 from SupplierShopPo d  where d.supId = '" + supId + "' and d.shopId = o.shopId	)");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopUserPo> getShopUserPoByUserId(long userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from ShopUserPo o WHERE o.userId = '" + userId + "'");
		List<ShopUserPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopPo> getShopPoByShopId(long shopId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from ShopPo o WHERE o.shopId = '" + shopId + "'");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopPo> getShopPoByOrdId(long ordId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from ShopPo o WHERE EXISTS (SELECT 1 FROM OrderCustPo c WHERE c.ordCustId = o.shopId AND c.ordId ='" + ordId + "' )");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopRaisePricePo> getShopRaisePricePoByShopIdMaterTypeSupId(long shopId, int materType, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from ShopRaisePricePo o WHERE o.shopId = '" + shopId + "' and o.materType = '" + materType + "' and supId = '" + supId
				+ "'");
		List<ShopRaisePricePo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopRaisePricePo> getShopRaisePricePoByRpid(long rpid) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from ShopRaisePricePo o WHERE o.rpid = '" + rpid + "'");
		List<ShopRaisePricePo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopRaisePricePo> getShopRaisePricePoByShopIdMaterIdTypeSupId(long shopId, int materId, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append("  SELECT o FROM ShopRaisePricePo o WHERE o.supId = '" + supId + "' AND o.shopId = '" + shopId
				+ "' AND EXISTS (SELECT 1 FROM Code c,MaterPo m WHERE c.codeTypeId = 'mater_type' AND c.codeOder = m.materType AND m.materId= '"
				+ materId + "' AND o.materType = m.materType)");
		List<ShopRaisePricePo> list = template.find(ql.toString(), null);
		return list;
	}
}
